

********************************************************************************
* This code is taken from the github of John Paul Ferguson
* Modifications to this code have been made
********************************************************************************



********************************************************************************
*
* cleanNLRBcsvs.do
* This file is meant to be run after clean_R.sh. Each block reads in one of the
* files representing a table in the CATS database, does some minimal cleaning
* such as de-stringing numeric variables, putting dates in Stata's date format,
* and encoding categorical variables, then compresses and saves out a Stata
* dataset. 
* 
* Further cleaning, such as encoding states and industries, or geocoding, are
* left as exercises for the user. Also, I do not have codebooks for each field
* in each table. For such details, I recommend contacting the NLRB directly, or
* consulting a book like McGuinnes & Norris's 1986 tome, "How to Take a Case
* Before the NLRB."
*
* Please note that this file calls several ADO files that I wrote to simplify
* the code: mdestring.ado, nlrbdate.ado, rencode.ado and swapout.ado. These need
* to be somewhere on Stata's ado-path.
*
********************************************************************************

clear
set more off


* Helper .ado files from Ferguson
do ${nlrb}/mdestring.ado
do ${nlrb}/nlrbdate.ado
do ${nlrb}/rencode.ado
do ${nlrb}/swapout.ado




// --------------------------------------------------------
// Closed case
// --------------------------------------------------------

	clear
	insheet using ${raw}/nlrb/combined/R_CLOSED_CASE.csv, c n
	drop if r_case_number == "R_Case_Number"
	
	quietly do ${nlrb}/nlrbLabels.do
	
	mdestring action_sequence
	nlrbdate closing_date date_file_archives date_file_to_records date_file_destroyed
	rencode closing_stage, label(closing_stage) noextend
	rencode closing_method, label(closing_method) noextend
	
	compress
	save  ${raw}/nlrb/R_CLOSED_CASE.dta, replace
	
// --------------------------------------------------------
// certification
// --------------------------------------------------------
	
	clear
	insheet using  ${raw}/nlrb/combined/R_ELECT_CERTIFICATION.csv, c n
	drop if election_id == "Election_ID"
	
	quietly do ${nlrb}/nlrbLabels.do
	nlrbdate cert_of_representative cert_of_results
	
	compress
	save  ${raw}/nlrb/R_ELECT_CERTIFICATION.dta, replace
	

// --------------------------------------------------------
// Elect votes for
// --------------------------------------------------------
	
	clear
	insheet using ${raw}/nlrb/combined/R_ELECT_VOTES_FOR.csv, c n
	drop if r_case_number == "R_Case_Number"
	
	quietly do ${nlrb}/nlrbLabels.do
	mdestring tally_id sort_order votes_for
	rencode unit_id, label(unit_id) noextend
	
	compress
	save  ${raw}/nlrb/R_ELECT_VOTES_FOR.dta, replace


// --------------------------------------------------------
// Election Tally
// --------------------------------------------------------
	
	clear
	insheet using ${raw}/nlrb/combined/R_ELECTION_TALLY.csv, c n
	drop if r_case_number == "R_Case_Number"
	
	quietly do ${nlrb}/nlrbLabels.do
	mdestring tally_id num_employees_eligible num_void_ballots num_votes_against ///
		num_valid_votes num_challenges num_for_inclusion num_against_inclusion ///
		num_sustained_challenges
	nlrbdate tally_date
	rencode unit_id, label(unit_id) noextend
	rencode tally_type, label(type_tally) noextend
	rencode self_determination_tally, label(yesno) noextend
	rencode challenges_determinative, label(yesno) noextend
	rencode runoff_required, label(yesno) noextend
	
	compress
	save  ${raw}/nlrb/R_ELECTION_TALLY.dta, replace


// --------------------------------------------------------
// CATs-FRF
// --------------------------------------------------------	
	
	clear
	insheet using ${raw}/nlrb/CATS-FRF-R.csv, c n
	drop if r_case_number == "R_Case_Number"
	
	quietly do ${nlrb}/nlrbLabels.do
	replace election_results = "LOSS" if election_results == "LOST"
	split closing_method
	replace closing_method = closing_method1
	drop closing_method1-closing_method4
	split naics_code
	replace naics_code = naics_code1
	drop naics_code1-naics_code14
	mdestring naics_code num_elig_employees num_valid_votes num_votes_for ///
		num_votes_against
	nlrbdate tally_ballot_date date_election date_filed date_closed
	rencode status, label(status) noextend
	rencode election_results, label(winloss) noextend
	rencode closing_method, label(closing_method) noextend
	rencode unit_id, label(unit_id) noextend
	rencode bargaining_unit_code, label(barg_unit_code) noextend
	rencode tally_type, label(type_tally) noextend
	rencode multiunion, label(yesno) noextend
	
	compress
	save  ${raw}/nlrb/CATS-R-FRF.dta, replace	
